﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Web.Http;

namespace Waiyun.WebApi
{
    public class ExportationSDController : ApiController
    {
        public static string ConnectionString;

        static ExportationSDController()
        {
            ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["waiyun_v2"].ConnectionString;
        }

        /// <summary>
        /// 委托
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public SingleResult<long> Delegate()
        {
            string name = "ExportationSD\\Delegate";
            if (ThreadPools.Get(name) == null)
            {
                Thread thread = new Thread(() =>
                {
                    string errorMessage = null;
                    OracleConnection connection = ConnectionPool.GetConnection(ConnectionString, out errorMessage);
                    if (connection == null)
                    {
                        return;
                    }
                    try
                    {
                        long value = 0;
                        using (OracleCommand command = new OracleCommand(@"select count(1)
  from OD_F_STATS od
 where od.d_order_time > sysdate - 30
   and od.n_accept_num > 0
   and od.v_org_id IN (select c.v_org_id
                      from PRD_DW.DF_SINO_COMPANY c
                     where c.v_secon_org = '山东'
                       and c.v_is_order = 'Y')", connection))
                        {
                            using (OracleDataReader reader = command.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    value = Convert.ToInt64(reader[0]);
                                }
                                reader.Close();
                                Cache2.Set(name, value);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        return;
                    }
                    finally
                    {
                        ConnectionPool.Release(connection);
                    }
                    ThreadPools.Stop(name);
                });
                thread.Name = name;
                ThreadPools.Start(thread);
            }
            object cacheValue = Cache2.Get(name);
            if (cacheValue != null)
            {
                return new SingleResult<long>(Convert.ToInt64(cacheValue));
            }
            return new SingleResult<long>(0);
        }

        /// <summary>
        /// 委托
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public SingleResult<long> DelegateCust()
        {
            string name = "ExportationSD\\DelegateCust";
            if (ThreadPools.Get(name) == null)
            {
                Thread thread = new Thread(() =>
                {
                    string errorMessage = null;
                    OracleConnection connection = ConnectionPool.GetConnection(ConnectionString, out errorMessage);
                    if (connection == null)
                    {
                        return;
                    }
                    try
                    {
                        long value = 0;
                        using (OracleCommand command = new OracleCommand(@"select count(1)
  from (select distinct (V_STANDARD_CUST_ID)
          from PRD_DW.DF_ORDER_STATUS_HEAD h
         where h.d_create_time > sysdate - 30
           and h.V_STANDARD_CUST_ID in
               ('ESDY00101122',
                'ESDY00100247',
                'ESDH00001238',
                'ESDY00101058',
                'ESDY00100639',
                'ETJF00001592',
                'ESDY00101572',
                'ETJF00001213',
                'ETJF00001177')
           and V_STANDARD_ORG IN
               (select c.v_org_id
                  from PRD_DW.DF_SINO_COMPANY c
                 where c.v_secon_org = '山东'
                   and c.v_is_order = 'Y'))", connection))
                        {
                            using (OracleDataReader reader = command.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    value = Convert.ToInt64(reader[0]);
                                }
                                reader.Close();
                                Cache2.Set(name, value);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        return;
                    }
                    finally
                    {
                        ConnectionPool.Release(connection);
                    }
                    ThreadPools.Stop(name);
                });
                thread.Name = name;
                ThreadPools.Start(thread);
            }
            object cacheValue = Cache2.Get(name);
            if (cacheValue != null)
            {
                return new SingleResult<long>(Convert.ToInt64(cacheValue));
            }
            return new SingleResult<long>(0);
        }

        /// <summary>
        /// 通关
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public SingleResult<long> Clearance()
        {
            string name = "ExportationSD\\Clearance";
            if (ThreadPools.Get(name) == null)
            {
                Thread thread = new Thread(() =>
                {
                    string errorMessage = null;
                    OracleConnection connection = ConnectionPool.GetConnection(ConnectionString, out errorMessage);
                    if (connection == null)
                    {
                        return;
                    }
                    try
                    {
                        long value = 0;
                        using (OracleCommand command = new OracleCommand(@"select count(1)
  from OD_F_STATS o
 where o.N_CTDCL_NUM = 1
   and o.N_CUST_CLER_NUM = 0
   and o.d_order_time > sysdate - 30
   and o.v_org_id IN (select c.v_org_id
                        from PRD_DW.DF_SINO_COMPANY c
                       where c.v_secon_org = '山东'
                         and c.v_is_order = 'Y')", connection))
                        {
                            using (OracleDataReader reader = command.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    value = Convert.ToInt64(reader[0]);
                                }
                                reader.Close();
                                Cache2.Set(name, value);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        return;
                    }
                    finally
                    {
                        ConnectionPool.Release(connection);
                    }
                    ThreadPools.Stop(name);
                });
                thread.Name = name;
                ThreadPools.Start(thread);
            }
            object cacheValue = Cache2.Get(name);
            if (cacheValue != null)
            {
                return new SingleResult<long>(Convert.ToInt64(cacheValue));
            }
            return new SingleResult<long>(0);
        }
        /// <summary>
        /// 待装船
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public SingleResult<long> Loading()
        {
            string name = "ExportationSD\\Loading";
            if (ThreadPools.Get(name) == null)
            {
                Thread thread = new Thread(() =>
                {
                    string errorMessage = null;
                    OracleConnection connection = ConnectionPool.GetConnection(ConnectionString, out errorMessage);
                    if (connection == null)
                    {
                        return;
                    }
                    try
                    {
                        long value = 0;
                        using (OracleCommand command = new OracleCommand(@"select count(1)
  from OD_F_STATS o
 where o.N_OCGAT_NUM = 1
   and o.N_LOADING_NUM = 0
   and o.d_order_time > sysdate - 30
   and o.v_org_id IN (select c.v_org_id
                        from PRD_DW.DF_SINO_COMPANY c
                       where c.v_secon_org = '山东'
                         and c.v_is_order = 'Y')", connection))
                        {
                            using (OracleDataReader reader = command.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    value = Convert.ToInt64(reader[0]);
                                }
                                reader.Close();
                                Cache2.Set(name, value);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        return;
                    }
                    finally
                    {
                        ConnectionPool.Release(connection);
                    }
                    ThreadPools.Stop(name);
                });
                thread.Name = name;
                ThreadPools.Start(thread);
            }
            object cacheValue = Cache2.Get(name);
            if (cacheValue != null)
            {
                return new SingleResult<long>(Convert.ToInt64(cacheValue));
            }
            return new SingleResult<long>(0);
        }

        /// <summary>
        /// 在途中
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public SingleResult<long> Transiting()
        {
            string name = "ExportationSD\\Transiting";
            if (ThreadPools.Get(name) == null)
            {
                Thread thread = new Thread(() =>
                {
                    string errorMessage = null;
                    OracleConnection connection = ConnectionPool.GetConnection(ConnectionString, out errorMessage);
                    if (connection == null)
                    {
                        return;
                    }
                    try
                    {
                        long value = 0;
                        using (OracleCommand command = new OracleCommand(@"select count(1)
  from OD_F_STATS o
 where o.N_LOADING_NUM = 1
   and o.N_DISCHARGE_NUM = 0
   and o.d_order_time > sysdate - 30
   and o.v_org_id IN (select c.v_org_id
                        from PRD_DW.DF_SINO_COMPANY c
                       where c.v_secon_org = '山东'
                         and c.v_is_order = 'Y')", connection))
                        {
                            using (OracleDataReader reader = command.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    value = Convert.ToInt64(reader[0]);
                                }
                                reader.Close();
                                Cache2.Set(name, value);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        return;
                    }
                    finally
                    {
                        ConnectionPool.Release(connection);
                    }
                    ThreadPools.Stop(name);
                });
                thread.Name = name;
                ThreadPools.Start(thread);
            }
            object cacheValue = Cache2.Get(name);
            if (cacheValue != null)
            {
                return new SingleResult<long>(Convert.ToInt64(cacheValue));
            }
            return new SingleResult<long>(0);
        }

        [HttpGet]
        public SingleResult<Data<string, long>> XYOrder()
        {
            Data<string, long> cacheData = Cache.Get("ExportationSD-XYORDER") as Data<string, long>;
            if (cacheData != null)
            {
                return new SingleResult<Data<string, long>>(cacheData);
            }

            OracleConnection connection = new OracleConnection(ConnectionString);
            try
            {
                connection.Open();

                using (OracleCommand command = new OracleCommand(@"select count(1), to_char(o.d_order_time, 'yyyy-mm-dd')
  from OD_F_STATS o
 where o.d_order_time > sysdate - 30
   and o.v_org_id IN (select c.v_org_id
                        from PRD_DW.DF_SINO_COMPANY c
                       where c.v_secon_org = '山东'
                         and c.v_is_order = 'Y')
 group by to_char(o.d_order_time, 'yyyy-mm-dd') 　　order by to_char(o.d_order_time, 'yyyy-mm-dd')", connection))
                {
                    OracleDataAdapter adapter = new OracleDataAdapter(command);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);

                    Data<string, long> data = new Data<string, long>();
                    for (int index = 0; index < dataTable.Rows.Count; index++)
                    {
                        data.XValues.Add(Convert.ToString(dataTable.Rows[index][1]));
                        data.YValues.Add(Convert.ToInt64(dataTable.Rows[index][0]));
                    }

                    Cache.Set("ExportationSD-XYORDER", data);
                    return new SingleResult<Data<string, long>>(data);
                }
            }
            catch (Exception ex)
            {
                return new SingleResult<Data<string, long>>(1, ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        [HttpGet]
        public SingleResult<Data<string, long>> XYCust()
        {
            Data<string, long> cacheData = Cache.Get("ExportationSD-XYCUST") as Data<string, long>;
            if (cacheData != null)
            {
                return new SingleResult<Data<string, long>>(cacheData);
            }

            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                try
                {
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(@"select (case h.V_STANDARD_CUST_ID 
when 'ESDH00001238' then '魏桥纺织'
when 'ESDY00100247' then '青岛前程环球海空通运'
when 'ESDY00100639' then '青岛和川国际物流'
when 'ESDY00101058' then '青岛瑞福达'
when 'ESDY00101122' then '青岛浩宏'
when 'ESDY00101572' then '山东澳源达'
when 'ETJF00001177' then '天津德迅'
when 'ETJF00001213' then '天津敦豪'
when 'ETJF00001592' then '天津海斯陆'
else h.V_STANDARD_CUST_ID end) as 客户,count(1) as SUM from  PRD_DW.DF_ORDER_STATUS_HEAD h where  h.d_create_time > sysdate-30 group by h.V_STANDARD_CUST_ID 
having h.V_STANDARD_CUST_ID in ('ESDY00101122','ESDY00100247','ESDH00001238','ESDY00101058','ESDY00100639','ETJF00001592','ESDY00101572','ETJF00001213','ETJF00001177')
order by SUM desc", connection))
                    {
                        using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                        {
                            DataTable dataTable = new DataTable();
                            adapter.Fill(dataTable);

                            Data<string, long> data = new Data<string, long>();
                            for (int index = 0; index < dataTable.Rows.Count; index++)
                            {
                                data.XValues.Add(Convert.ToString(dataTable.Rows[index][0]));
                                data.YValues.Add(Convert.ToInt64(dataTable.Rows[index][1]));
                            }

                            Cache.Set("ExportationSD-XYCUST", data);
                            return new SingleResult<Data<string, long>>(data);
                        }
                    }
                }
                catch (Exception ex)
                {
                    return new SingleResult<Data<string, long>>(1, ex.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}
